package httpeter.misc;

import java.sql.*;

/**
 *
 * @author Peter Hendriks, 2008 Simple MySQL Database Connector class. This
 * class requires the presence of the Sun jdbc:myqsl driver. It allows you to
 * create database applications fast while
 * not having to connect and handle drivers and connection strings all the
 * time.
 *
 * Do not forget to include the proper driver libraries in your project.
 * For questions or suggestions please contact me at: httpeter@gmail.com
 *
 * Happy Programming!
 */

public class DBConnector
{
    //Private vars...
    private Statement stmt;
    private Connection conn;
    private String jdbc = "jdbc:mysql://", driver = "com.mysql.jdbc.Driver", dbLocation = "localhost", dbUser = null, dbPassword = null, dbName = null;
    private StringBuilder sessionLog;
    private StringBuilder connectionString;
    private boolean autoConnect = true;
    private boolean showConsoleOutput = true;
    private boolean Connected = false;
    private int dbPort = 3306;
    
    //<editor-fold defaultstate="collapsed" desc="Gettors 'n Settors...">
    public void setDbName(String dbName)
    {
        this.dbName = dbName;
    }
    
    public void setDbPassword(String dbPassword)
    {
        this.dbPassword = dbPassword;
    }
    
    public void setDbUser(String dbUser)
    {
        this.dbUser = dbUser;
    }
    
    /**
     * Default is 'true'
     *
     * @param showConsoleOutput
     */
    public void setShowConsoleOutput(boolean showConsoleOutput)
    {
        this.showConsoleOutput = showConsoleOutput;
    }
    //</editor-fold>

    /**
     * Constructor...
     */
    public DBConnector()
    {
        sessionLog = new StringBuilder();
    }

    /**
     * Connecting using the variables above. Not nescessary if the 'autologin'
     * variable = true; which is the case by default.
     */
    public void Connect()
    {
        if (Connected)
        {
            Echo("Already connected to DB");
        } else
        {
            try
            {
                connectionString = new StringBuilder(jdbc);
                connectionString.append(dbLocation);
                connectionString.append(":");
                connectionString.append(dbPort);
                connectionString.append("/");
                connectionString.append(dbName);
                Class.forName(driver);
                conn = DriverManager.getConnection(connectionString.toString(), dbUser, dbPassword);
                Connected = true;
                Echo("Database connection established with: ");
                Echo(connectionString.toString());
            } catch (Exception e0)
            {
                Connected = false;
                Echo(e0.toString());
            }
        }
    }

    /**
     * Disconnecting returning possible errors... Use before your app. is closed
     */
    public void Disconnect()
    {
        if (!Connected)
        {
            Echo("DB already disconnected");
        } else
        {
            try
            {
                conn.close();
                stmt.close();
            } catch (Exception e0)
            {
                Connected = true;
                Echo("DB failed to disconnect");
            }
            Connected = false;
            Echo("DB disconnected");
        }
    }

    /**
     * Use this method for Select Queries only...
     *
     * @param sql
     * @return
     */
    public ResultSet ExecuteSelectStatement(String sql)
    {
        if (autoConnect)
        {
            Connect();
        }
        ResultSet rs = null;
        if (Connected)
        {
            try
            {
                stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                Echo("Statement successfully executed: ");
                Echo(sql);
                return rs;
            } catch (Exception e0)
            {
                Echo(e0.toString());
                Disconnect();
                return rs;
            }
        } else
        {
            Echo("Not connected to DB");
            return rs;
        }
    }

    /**
     * Use this method for Update statements....
     *
     * @param sql
     */
    public void ExecuteUpdateStatement(String sql)
    {
        if (autoConnect)
        {
            Connect();
        }
        if (Connected)
        {
            StringBuilder msg = new StringBuilder();
            msg.append("Statement:\n\n");
            msg.append(sql);
            msg.append("\n\nSuccessfully Executed!");
            try
            {
                stmt = conn.createStatement();
                stmt.executeUpdate(sql);
                Echo(msg.toString());
                if (autoConnect)
                {
                    Disconnect();
                }
            } catch (Exception e0)
            {
                Echo(e0.toString());
                Disconnect();
            }
        } else
        {
            Echo("Not connected to DB");
        }
    }

    /**
     * Use this method for Insert statements....
     *
     * @param sql
     */
    public void ExecuteInsertStatement(String sql)
    {
        if (autoConnect)
        {
            Connect();
        }
        if (Connected)
        {
            StringBuilder msg = new StringBuilder();
            msg.append("Statement:\n\n");
            msg.append(sql);
            msg.append("\n\nSuccessfully Executed!");
            try
            {
                stmt = conn.createStatement();
                stmt.executeUpdate(sql);
                Echo(msg.toString());
                if (autoConnect)
                {
                    Disconnect();
                }
            } catch (Exception e0)
            {
                Echo(e0.toString());
                Disconnect();
            }
        } else
        {
            Echo("Not connected to DB");
        }
    }

    /**
     * Use to call stored procedures, not sure if it works on database other
     * than MySQL...
     *
     * @param procedureName
     * @return
     */
    public ResultSet ExecuteStoredProcedure(String procedureName)
    {
        if (autoConnect)
        {
            Connect();
        }
        ResultSet rs = null;
        if (Connected)
        {
            try
            {
                StringBuilder stmt = new StringBuilder();
                stmt.append("{call ");
                stmt.append(procedureName);
                stmt.append("}");
                CallableStatement proc = conn.prepareCall(stmt.toString());
                rs = proc.executeQuery();
                Echo("Stored Procedure successfully executed:");
                Echo(procedureName);
                if (autoConnect)
                {
                    //Disconnect();
                }
                return rs;
            } catch (Exception e0)
            {
                Echo(e0.toString());
                Disconnect();
                return rs;
            }
        } else
        {
            Echo("Not connected to DB");
            return rs;
        }
    }

    /**
     * Appends the output of the differnt methods into the 'sessionLog'
     * variables If the 'showConsoleOutput' variable==true; which is the
     * case by default, then the output will always be shown.
     */
    private void Echo(String msg)
    {
        sessionLog.append(msg);
        sessionLog.append("\n");
        if (showConsoleOutput)
        {
            System.out.println(msg);
        }
    }
}
